import pandas as pd
from tools.debug_tools import *
from openpyxl.utils import get_column_letter

# 中文算2个长度, 英文和数字算1个
def get_len(uni_str):
	uni_str = str(uni_str) # 以防不是str
	len1 = len(uni_str)
	len2 = len(uni_str.encode('utf-8'))
	return int((len2 + len1) / 2) + 1;	 # 预留空间

# 用于让excel表格列宽自适应
# columns 和 data 都是list
@aspect_logging
def col_autofit(writer, data, columns=None, sheet_name='Sheet1'):
	m = len(data)										# 行数
	n = len(columns) if columns is not None else None	# 列数
	widths = [0 for i in range(n)]
	for j in range(n):
		widths[j] = max(widths[j], get_len(columns[j]))	if columns is not None else widths[j]
		for i in range(m):
			widths[j] = max(widths[j], get_len(data[i][j]))
		widths[j] = 25 if widths[j] > 25 else widths[j]
		widths[j] = 10 if widths[j] < 10 else widths[j]
	for i in range(n):
		writer.sheets[sheet_name].column_dimensions[get_column_letter(i+1)].width = widths[i]
	return writer

# 封装好的读excel的函数
@aspect_logging
def read_excel(name, sheet_name='Sheet1', ret_type='array'):
	df = pd.read_excel(name, sheet_name=sheet_name, keep_default_na=False)
	if ret_type == 'list':
		data = df.values.tolist()
		columns = df.columns.values.tolist()
	elif ret_type == 'array':
		data = df.values
		columns = df.columns.values
	elif ret_type == 'df':
		data = df
		columns = None
	return data, columns

# 封装好的写excel的函数
@aspect_logging
def write_excel(data, columns, name, sheet_name='Sheet1', in_type='array', autofit=False):
	if in_type in ['list', 'array']:
		df = pd.DataFrame(data, columns=columns)
	elif in_type == 'df':
		df = data
	else:
		print('%s is not supported in_type' % in_type)
		return None
	writer = pd.ExcelWriter(name)
	df.to_excel(writer, sheet_name=sheet_name, index=False)
	if autofit == True:
		col_autofit(writer, df.values, df.columns.values, sheet_name=sheet_name).save()
	else:
		writer.save()
